
#====打开及读取表格内容====
#一、读
#1.打开Excel表格并获取表格名称

# =============================================================================
# from openpyxl import load_workbook
# workbook = load_workbook(filename = "test.xlsx")
# workbook.sheetnames
# =============================================================================

#2.通过sheet名称获取表格

# =============================================================================
# from openpyxl import load_workbook
# workbook = load_workbook(filename = "test.xlsx")
# workbook.sheetnames
# sheet = workbook["Sheet1"]
# print(sheet)
# 
# =============================================================================
#3.获取表格尺寸大小

# =============================================================================
# print(sheet.dimensions)
# =============================================================================

#4.获取表格内某个格子的数据

# =============================================================================
# sheet = workbook.active   #打开激活的表格
# print(sheet)
# cell1 = sheet["A1"]   #获取A1格子的数据，也可(row = 1,column = 1)
# cell2 = sheet["C11"]
# print(cell1.value, cell2.value)  # cell.value 获取格子中的值
# =============================================================================

#5.获取某个格子的行数、列数、坐标

# =============================================================================
# print(cell1.value, cell1.row, cell1.column, cell1.coordinate)
# print(cell2.value, cell2.row, cell2.column, cell2.coordinate)
# =============================================================================



#6.获取一系列格子
# 1）sheep[]方式，获取A1:C2区域的值

# =============================================================================
# cell = sheet["A1:C2"]
# print(cell)
# for i in cell:
#     for j in i:
#         print(j.value)
# =============================================================================

#2）.iter_rows()和.iter_cols()方式

# 按行获取值

# =============================================================================
# for i in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=2):
#     for j in i:
#         print(j.value)
# # 按列获取值
# for i in sheet.iter_cols(min_row=2, max_row=5, min_col=1, max_col=2):
#     for j in i:
#         print(j.value)   
# =============================================================================

#3）获取所有行

# =============================================================================
# for i in sheet.rows:
#         print(i)
# 
# =============================================================================



#二、写
#1. 修改表格中的内容
# ① 向某个格子中写入内容并保存

# =============================================================================
# sheet["A1"] = "哈喽"
# workbook.save(filename = "test01.xlsx")
# 
# =============================================================================
#② .append()：向表格中插入行数据

# =============================================================================
# data = [
#     ["唐僧","男","180cm"],
#     ["孙悟空","男","188cm"],
#     ["猪八戒","男","175cm"],
#     ["沙僧","男","176cm"],
# ]
# for row in data:
#     sheet.append(row)
# workbook.save(filename = "test02.xlsx")
# =============================================================================


#③ 在python中使用excel函数公式(很有用)
# =============================================================================
# 
# sheet["D1"] = "标准身高"
# for i in range(2,7):
#     sheet["D{}".format(i)] = '=IF(RIGHT(C{},2)="cm",C{},SUBSTITUTE(C{},"m","")*100&"cm")'.format(i,i,i)
# workbook.save(filename = "test03.xlsx")
# 
# =============================================================================

#excel函数公式

# =============================================================================
# from openpyxl.utils import FORMULAE
# print(FORMULAE)
# =============================================================================


#④ .insert_cols()和.insert_rows()：插入空行和空列

# =============================================================================
# sheet.insert_cols(idx=2,amount=2)
# sheet.insert_rows(idx=2,amount=2)
# workbook.save(filename = "test04.xlsx")
# =============================================================================

#⑤ .delete_rows()和.delete_cols()：删除行和列

# =============================================================================
# sheet.delete_cols(idx=1)
# sheet.delete_rows(idx=1)
# workbook.save(filename = "test05.xlsx")
# =============================================================================

#⑥ .move_range()：移动格子

# =============================================================================
# sheet.move_range("A1:A6",rows=2,cols=3)  # 向左移动两列，向下移动两行
# workbook.save(filename = "test06.xlsx")
# =============================================================================

#⑦ .create_sheet()：创建新的sheet表格

# =============================================================================
# workbook.create_sheet("我是一个新的sheet")
# print(workbook.sheetnames)
# workbook.save(filename = "test.xlsx")
# =============================================================================

#⑧ .remove()：删除某个sheet表

# =============================================================================
# sheet1 = workbook['我是一个新的sheet']  
# print(sheet1)
# workbook.remove(sheet1)
# print(workbook.sheetnames)
# workbook.save(filename = "test.xlsx")
# =============================================================================

#⑨ .copy_worksheet()：复制一个sheet表到另外一张excel表

# =============================================================================
# print("test.xlsx中有这几个sheet表",workbook.sheetnames)
# sheet = workbook['Sheet2']  
# workbook.copy_worksheet(sheet)
# workbook.save(filename = "test.xlsx")
# =============================================================================

#⑩ sheet.title：修改sheet表的名称

# =============================================================================
# sheet = workbook['Sheet3']
# print(sheet)
# sheet.title = "改后Sheet3"
# workbook.save(filename = "test.xlsx")
# =============================================================================

#⑪ 创建新的excel表格文件

# =============================================================================
# sheet.title = "表格1"
# workbook.save(filename = "test07.xlsx")
# =============================================================================

#⑫ sheet.freeze_panes：冻结窗口

# =============================================================================
# sheet.freeze_panes = "C1"
# workbook.save(filename = "test08.xlsx")
# =============================================================================



#三、批量调整字体和样式
from openpyxl.styles import Alignment
from openpyxl.styles import PatternFill,GradientFill
from openpyxl.styles import Font
from openpyxl import load_workbook

# workbook = load_workbook(filename="test.xlsx")
# sheet = workbook.active

#1. 修改表格中的内容
# =============================================================================
# cell = sheet["A1"]
# font = Font(name="微软雅黑",size=20,bold=True,italic=True,color="FF0000")
# cell.font = font
# workbook.save(filename = "test.xlsx")
# =============================================================================

#2. 获取表格中格子的字体样式
# =============================================================================
# cell = sheet["A2"]
# font = cell.font
# print(font.name, font.size, font.bold, font.italic, font.color)
# =============================================================================
#3. 设置对齐样式
# =============================================================================
# cell = sheet["A1"]
# alignment = Alignment(horizontal="center",vertical="center",text_rotation=45)
# cell.alignment = alignment
# workbook.save(filename = "test.xlsx")
# =============================================================================
#4. 设置填充样式

# =============================================================================
# cell_b9 = sheet["A3"]
# pattern_fill = PatternFill(fill_type="solid",fgColor="99ccff")
# cell_b9.fill = pattern_fill
# cell_b10 = sheet["A4"]
# gradient_fill = GradientFill(stop=("FFFFFF","99ccff","000000"))
# cell_b10.fill = gradient_fill
# workbook.save(filename = "test.xlsx")
# =============================================================================
#5.设置行高和列宽
# =============================================================================
# # 设置第1行的高度
# sheet.row_dimensions[1].height = 50
# # 设置B列的宽度
# sheet.column_dimensions["B"].width = 20
# workbook.save(filename = "test.xlsx")
# =============================================================================
#6.合并单元格
# =============================================================================
# sheet.merge_cells("C1:D2")
# sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=3)
# workbook.save(filename = "test.xlsx")
# =============================================================================












